﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using ZhCun.Utils.Helpers;

namespace ZhCun.Utils
{
    public class SqlScriptTool
    {
        static void SetInsertValues(StringBuilder sql, string[] colNames, object m, string[] ignoreFields)
        {
            sql.Append("select ");
            for (int j = 0; j < colNames.Length; j++)
            {
                var colName = colNames[j];
                if (ignoreFields?.Contains(colName) == true) continue;
                if (j > 0)
                {
                    sql.Append(",");
                }

                var val = ReflectionHelper.GetPropertyValue(m, colName);
                if (val == null || val == DBNull.Value)
                {
                    sql.Append("Null");
                    continue;
                }
                var valType = val.GetType();
                if (val is DateTime dt)
                {
                    sql.Append($"'{dt:yyyy-MM-dd HH:mm:ss.fff}'");
                }
                else if (valType == typeof(DateTime?))
                {
                    var r = val as DateTime?;
                    sql.Append($"'{r.Value:yyyy-MM-dd HH:mm:ss.fff}'");
                }
                else if (val is bool bVal)
                {
                    sql.AppendFormat("{0}", bVal ? 1 : 0);
                }
                else if (valType == typeof(bool?))
                {
                    var r = val as bool?;
                    sql.AppendFormat("{0}", r.Value ? 1 : 0);
                }
                else if (valType.IsNumericType() || valType.IsNumericType())
                {
                    sql.AppendFormat(val.ToString());
                }
                else
                {
                    sql.Append($"'{val}'");
                }
            }
        }

        static void SetInsertInto(StringBuilder sql, string tbName, string[] colNames, string[] ignoreFields)
        {
            sql.Append($"Insert Into {tbName}(");
            for (int i = 0; i < colNames.Length; i++)
            {
                var colName = colNames[i];
                if (ignoreFields?.Contains(colName) == true) continue;
                if (i > 0)
                {
                    sql.Append(",");
                }
                sql.Append($"{colName}");
            }
            sql.AppendLine(")");
        }

        public static string GetInsertSQL<T>(List<T> srcList, params string[] ignoreFields) where T : class, new()
        {
            if (srcList == null || srcList.Count == 0) return null;
            var colNames = ReflectionHelper.GetPropertyNames<T>();
            StringBuilder sql = new StringBuilder();
            var tbName = typeof(T).Name;
            sql.Append($"Insert Into {tbName}(");
            for (int i = 0; i < colNames.Length; i++)
            {
                var colName = colNames[i];
                if (ignoreFields?.Contains(colName) == true) continue;
                if (i > 0)
                {
                    sql.Append(",");
                }
                sql.Append($"{colName}");
            }
            sql.AppendLine(")");

            for (int i = 0; i < srcList.Count; i++)
            {
                if (i > 0)
                {
                    sql.AppendLine(" union all ");
                }
                var m = srcList[i];

                sql.Append("select ");
                for (int j = 0; j < colNames.Length; j++)
                {
                    var colName = colNames[j];
                    if (ignoreFields?.Contains(colName) == true) continue;
                    if (j > 0)
                    {
                        sql.Append(",");
                    }

                    var val = ReflectionHelper.GetPropertyValue(m, colName);
                    if (val == null || val == DBNull.Value)
                    {
                        sql.Append("Null");
                        continue;
                    }
                    var valType = val.GetType();
                    if (val is DateTime dt)
                    {
                        sql.Append($"'{dt:yyyy-MM-dd HH:mm:ss.fff}'");
                        continue;
                    }
                    else if (valType == typeof(DateTime?))
                    {
                        var r = val as DateTime?;
                        sql.Append($"'{r.Value:yyyy-MM-dd HH:mm:ss.fff}'");
                        continue;
                    }
                    else if (val is bool bVal)
                    {
                        sql.AppendFormat("{0}", bVal ? 1 : 0);
                    }
                    else if (valType == typeof(bool?))
                    {
                        var r = val as bool?;
                        sql.AppendFormat("{0}", r.Value ? 1 : 0);
                    }
                    else if (valType.IsNumericType() || valType.IsNumericType())
                    {
                        sql.AppendFormat(val.ToString());
                    }
                    else
                    {
                        sql.Append($"'{val}'");
                    }
                }
            }

            return sql.ToString();
        }

    }
}